<%@ page language="java" contentType="text/javascript; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page import='java.sql.*' %>
<%@page import="java.util.ArrayList"%>
<%@ page import="java.util.Calendar"%>
<%@ page import="java.util.Date"%>
<%@ page import="java.text.DecimalFormat"%>
<%@ page import="java.text.SimpleDateFormat"%>
<%@page import="net.sf.json.JSONObject"%>
<%@page import="net.sf.json.JSONSerializer"%>
<%@page import="net.sf.json.JSONArray"%>

<%String callback = request.getParameter("callback");%>
<%=callback %> (<%

	String getLat = request.getParameter("userlat");
	String getLng = request.getParameter("userlng");
	int getTheme = Integer.parseInt(request.getParameter("usertheme"));
	//int getTheme = 1;
	
	String p = request.getParameter("pages"); 
    String rpp = request.getParameter("rpp");
    String start = request.getParameter("start");
    int rpps = Integer.parseInt(rpp);
    int starts = Integer.parseInt(start);
	
	String select;
	JSONObject jo2 = new JSONObject();
	Connection conn = null;
	PreparedStatement pstmt = null;
	
	
	try{
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection(
			"jdbc:mysql://localhost/itscreater", "itscreater", "campus123");
		switch (getTheme) {
		case 1:
			select = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
					" from (select Number, ID, Host, Title, TingTheme, Lat, Lng, Content, Date," + 
					" ( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ) AS distance,"+
					" TIMESTAMPDIFF(MINUTE, Date, NOW()) AS time,"+
					" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, Date, NOW()), 2) AS timescore,"+
					" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ), 2) AS distancescore,"+
					" CASE TingTheme WHEN 1 THEN 20 WHEN 2 THEN 17 WHEN 3 THEN 8 WHEN 4 THEN 3 WHEN 5 THEN 15 WHEN 6 THEN 3 ELSE 20 END AS theme"+
					" from Ting WHERE Date > CURRENT_DATE( ) - INTERVAL 2 DAY) a ORDER BY totalvalue DESC limit ?, ?;";
			pstmt = conn.prepareStatement(select);
			pstmt.setString(1, getLat);
			pstmt.setString(2, getLng);
			pstmt.setString(3, getLat);
			pstmt.setString(4, getLat);
			pstmt.setString(5, getLng);
			pstmt.setString(6, getLat);
			pstmt.setInt(7, starts);
			pstmt.setInt(8, rpps);
			break;
		case 2:
			select = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
					" from (select Number, ID, Host, Title, TingTheme, Lat, Lng, Content, Date," + 
					" ( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ) AS distance,"+
					" TIMESTAMPDIFF(MINUTE, Date, NOW()) AS time,"+
					" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, Date, NOW()), 2) AS timescore,"+
					" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ), 2) AS distancescore,"+
					" CASE TingTheme WHEN 1 THEN 17 WHEN 2 THEN 20 WHEN 3 THEN 15 WHEN 4 THEN 3 WHEN 5 THEN 8 WHEN 6 THEN 15 ELSE 20 END AS theme"+
					" from Ting WHERE Date > CURRENT_DATE( ) - INTERVAL 2 DAY) a ORDER BY totalvalue DESC limit ?, ?;";
			pstmt = conn.prepareStatement(select);
			pstmt.setString(1, getLat);
			pstmt.setString(2, getLng);
			pstmt.setString(3, getLat);
			pstmt.setString(4, getLat);
			pstmt.setString(5, getLng);
			pstmt.setString(6, getLat);
			pstmt.setInt(7, starts);
			pstmt.setInt(8, rpps);
			break;
		case 3:
			select = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
					" from (select Number, ID, Host, Title, TingTheme, Lat, Lng, Content, Date," + 
					" ( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ) AS distance,"+
					" TIMESTAMPDIFF(MINUTE, Date, NOW()) AS time,"+
					" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, Date, NOW()), 2) AS timescore,"+
					" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ), 2) AS distancescore,"+
					" CASE TingTheme WHEN 1 THEN 8 WHEN 2 THEN 15 WHEN 3 THEN 20 WHEN 4 THEN 3 WHEN 5 THEN 5 WHEN 6 THEN 12 ELSE 20 END AS theme"+
					" from Ting WHERE Date > CURRENT_DATE( ) - INTERVAL 2 DAY) a ORDER BY totalvalue DESC limit ?, ?;";
			pstmt = conn.prepareStatement(select);
			pstmt.setString(1, getLat);
			pstmt.setString(2, getLng);
			pstmt.setString(3, getLat);
			pstmt.setString(4, getLat);
			pstmt.setString(5, getLng);
			pstmt.setString(6, getLat);
			pstmt.setInt(7, starts);
			pstmt.setInt(8, rpps);
			break;
		case 4:
			select = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
					" from (select Number, ID, Host, Title, TingTheme, Lat, Lng, Content, Date," + 
					" ( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ) AS distance,"+
					" TIMESTAMPDIFF(MINUTE, Date, NOW()) AS time,"+
					" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, Date, NOW()), 2) AS timescore,"+
					" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ), 2) AS distancescore,"+
					" CASE TingTheme WHEN 1 THEN 3 WHEN 2 THEN 3 WHEN 3 THEN 3 WHEN 4 THEN 20 WHEN 5 THEN 3 WHEN 6 THEN 3 ELSE 20 END AS theme"+
					" from Ting WHERE Date > CURRENT_DATE( ) - INTERVAL 2 DAY) a ORDER BY totalvalue DESC limit ?, ?;";
			pstmt = conn.prepareStatement(select);
			pstmt.setString(1, getLat);
			pstmt.setString(2, getLng);
			pstmt.setString(3, getLat);
			pstmt.setString(4, getLat);
			pstmt.setString(5, getLng);
			pstmt.setString(6, getLat);
			pstmt.setInt(7, starts);
			pstmt.setInt(8, rpps);
			break;
		case 5:
			select = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
					" from (select Number, ID, Host, Title, TingTheme, Lat, Lng, Content, Date," + 
					" ( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ) AS distance,"+
					" TIMESTAMPDIFF(MINUTE, Date, NOW()) AS time,"+
					" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, Date, NOW()), 2) AS timescore,"+
					" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ), 2) AS distancescore,"+
					" CASE TingTheme WHEN 1 THEN 15 WHEN 2 THEN 8 WHEN 3 THEN 5 WHEN 4 THEN 3 WHEN 5 THEN 20 WHEN 6 THEN 5 ELSE 20 END AS theme"+
					" from Ting WHERE Date > CURRENT_DATE( ) - INTERVAL 2 DAY) a ORDER BY totalvalue DESC limit ?, ?;";
			pstmt = conn.prepareStatement(select);
			pstmt.setString(1, getLat);
			pstmt.setString(2, getLng);
			pstmt.setString(3, getLat);
			pstmt.setString(4, getLat);
			pstmt.setString(5, getLng);
			pstmt.setString(6, getLat);
			pstmt.setInt(7, starts);
			pstmt.setInt(8, rpps);
			break;
		case 6:
			select = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
					" from (select Number, ID, Host, Title, TingTheme, Lat, Lng, Content, Date," + 
					" ( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ) AS distance,"+
					" TIMESTAMPDIFF(MINUTE, Date, NOW()) AS time,"+
					" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, Date, NOW()), 2) AS timescore,"+
					" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ), 2) AS distancescore,"+
					" CASE TingTheme WHEN 1 THEN 8 WHEN 2 THEN 15 WHEN 3 THEN 12 WHEN 4 THEN 3 WHEN 5 THEN 5 WHEN 6 THEN 20 ELSE 20 END AS theme"+
					" from Ting WHERE Date > CURRENT_DATE( ) - INTERVAL 2 DAY) a ORDER BY totalvalue DESC limit ?, ?;";
			pstmt = conn.prepareStatement(select);
			pstmt.setString(1, getLat);
			pstmt.setString(2, getLng);
			pstmt.setString(3, getLat);
			pstmt.setString(4, getLat);
			pstmt.setString(5, getLng);
			pstmt.setString(6, getLat);
			pstmt.setInt(7, starts);
			pstmt.setInt(8, rpps);
			break;			
		case 7:
			select = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
					" from (select Number, ID, Host, Title, TingTheme, Lat, Lng, Content, Date," + 
					" ( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ) AS distance,"+
					" TIMESTAMPDIFF(MINUTE, Date, NOW()) AS time,"+
					" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, Date, NOW()), 2) AS timescore,"+
					" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ), 2) AS distancescore,"+
					" 20 AS theme"+
					" from Ting WHERE Date > CURRENT_DATE( ) - INTERVAL 2 DAY) a ORDER BY totalvalue DESC limit ?, ?;";
			pstmt = conn.prepareStatement(select);
			pstmt.setString(1, getLat);
			pstmt.setString(2, getLng);
			pstmt.setString(3, getLat);
			pstmt.setString(4, getLat);
			pstmt.setString(5, getLng);
			pstmt.setString(6, getLat);
			pstmt.setInt(7, starts);
			pstmt.setInt(8, rpps);
			break;
		}
		
		ResultSet rs = pstmt.executeQuery();
		JSONObject jo = new JSONObject();
		JSONArray ja = new JSONArray();
		while (rs.next()) {
			String num = rs.getString("Number");
			String id = rs.getString("ID");
			String host = rs.getString("Host");
			String title = rs.getString("Title");
			String theme = rs.getString("TingTheme");
			String lat = rs.getString("Lat");
			String lng = rs.getString("Lng");
			String content = rs.getString("Content");
			if (title != null) {
				title = title.replace("\n","<br>");
				title = title.replace("\u0020","&nbsp;");
			}
			if (content != null) {
				content = content.replace("\n","<br>");
				content = content.replace("\u0020","&nbsp;");
			}
			String date = rs.getString("Date").substring(0,16);
			String getdistance = rs.getString("distance");
			String gettime = rs.getString("time");
			String gettheme = rs.getString("theme");
			String dot = "\\.";
			String gettotalvalue = rs.getString("totalvalue");
			String[] totalArray = gettotalvalue.split(dot);
			String finaltotal = totalArray[0];
			double totalvalue = Double.parseDouble(gettotalvalue);
			
			if (totalvalue >= 0 && totalvalue < 25){
				String wifi = "0";
				jo.put("Wifi", wifi);
			} else if (totalvalue >= 25 && totalvalue <50){
				String wifi = "1";
				jo.put("Wifi", wifi);
			} else if (totalvalue >= 50 && totalvalue <75){
				String wifi = "2";
				jo.put("Wifi", wifi);
			} else if (totalvalue >= 75 && totalvalue <= 100){
				String wifi = "3";
				jo.put("Wifi", wifi);
			}
			
			jo.put("NUMBER", num);
			jo.put("ID", id);
			jo.put("HOST", host);
			jo.put("TITLE", title);
			jo.put("THEME", theme);
			jo.put("LAT", lat);
			jo.put("LNG", lng);
			jo.put("CONTENT", content);
			jo.put("DATE", date);
			jo.put("Distance", getdistance);
			jo.put("Time", gettime);
			jo.put("ThemeScore", gettheme);
			jo.put("Total", finaltotal);
			jo.put("success", "true");
			
			ja.add(jo);
			jo2.put("results", ja);
		} 		
		
		String select2 = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
				" from (select Number, ID, Host, Title, TingTheme, Lat, Lng, Content, Date," + 
				" ( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ) AS distance,"+
				" TIMESTAMPDIFF(MINUTE, Date, NOW()) AS time,"+
				" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, Date, NOW()), 2) AS timescore,"+
				" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ), 2) AS distancescore,"+
				" 20 AS theme"+
				" from Ting WHERE Date > CURRENT_DATE( ) - INTERVAL 2 DAY) a ORDER BY totalvalue DESC;";
		pstmt = conn.prepareStatement(select2);
		pstmt.setString(1, getLat);
		pstmt.setString(2, getLng);
		pstmt.setString(3, getLat);
		pstmt.setString(4, getLat);
		pstmt.setString(5, getLng);
		pstmt.setString(6, getLat);
		
		ResultSet rs2 = pstmt.executeQuery();
		
		rs2.last();
		int total = rs2.getRow();
		rs2.beforeFirst();
		
		jo2.put("total", new Integer(total));
		out.print(jo2);	
	}
	
	finally{
		try{
			pstmt.close();
		}
		catch (Exception ignored){
		}
		try{
			conn.close();
		}
		catch (Exception ignored){
		}
	}
%>);